package test.com.sms.poc;


import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;

import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;


public class ExcelReaderExample {

	/**
	 * @param args
	 */
	public static void main(String[] args) throws Exception  {
		readWriteOldExcel();
		readWriteNewExcel();
	}
	
	private static void readWriteOldExcel() throws Exception   
	{
		Workbook readWorkbook = null;
		XSSFWorkbook writeNewWorkBook = null;
		
   	    FileOutputStream out = null;
   	    try
   	    {    		
	    	File inputWorkbookFile = new File("c://temp//ppt.xls");
	    	readWorkbook = Workbook.getWorkbook(inputWorkbookFile);
	   	    
	   	   	// New format
	   	   	writeNewWorkBook = new XSSFWorkbook();
		    XSSFSheet writeNewSheet = writeNewWorkBook.createSheet();
	      
	    	// Get the first sheet
	    	Sheet readSeet = readWorkbook.getSheet(1);
	
	    	for (int i = 0; i < readSeet.getRows(); i++) 
	    	{
	    		Row writeNewRow = writeNewSheet.createRow(i);
	    		
	    		for (int j = 0; j < readSeet.getColumns(); j++) 
	    		{
	    			// Old format
	    			Cell readCell = readSeet.getCell(j, i);
					org.apache.poi.ss.usermodel.Cell writeNewCell = writeNewRow.createCell(j);
					if (readCell != null && readCell.getContents().trim().length() > 0)
						writeNewCell.setCellValue(readCell.getContents());
					else
						writeBadCell(writeNewWorkBook, writeNewCell);
	    		}
	    	}
         
   	    	out = new FileOutputStream("c://temp//ppt_old_generated.xlsx");
   	    	writeNewWorkBook.write(out); 
   	    }
	 	catch(Exception e)
	 	{ 
	 		e.printStackTrace();
	 	}
	 	finally
	 	{
	 		out.close();
	 		readWorkbook.close();
	 		System.out.println("********************************* Done with readWriteOldExcel ****************************************");
	 	}
   	    
	}

    private static void readWriteNewExcel () throws Exception  
    {
    	int readIndex = 1;	
    	XSSFWorkbook readWorkbook = null;
    	XSSFWorkbook writeWorkBook = null;
    	
    	
    	FileInputStream in = null;
     	try
     	{             
     		in = new FileInputStream("c://temp//ppt.xlsx");
		    readWorkbook = new XSSFWorkbook(in); 
		    XSSFSheet readSheet = readWorkbook.getSheetAt(readIndex); 
		   
		    
		    writeWorkBook = new XSSFWorkbook();
		    XSSFSheet writeSheet = writeWorkBook.createSheet();
		    short rowNum = 0;
	
	
		    for (Row tempRow : readSheet) 
		    {     
		    	Row writeRow = writeSheet.createRow(rowNum++);
		    	
		    	int lastColumn = tempRow.getLastCellNum();
		    	// print out the first three columns    
		    	for (int column = 0; column < lastColumn; column++) 
		    	{        
		    		org.apache.poi.ss.usermodel.Cell readCell = tempRow.getCell(column);
		    		org.apache.poi.ss.usermodel.Cell writeCell = writeRow.createCell(column);
		    		if (readCell != null)
		    		{
		    			readCell.setCellType(org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING);
    					writeCell.setCellValue(readCell.getStringCellValue());
	
		    		}
		    		else
		    		{
		    			writeBadCell(writeWorkBook, writeCell);
		    		}
		    		
		    	}    
		    }
     	}
	    catch(Exception e)
    	{ 
    		e.printStackTrace();
    		throw e;
    	}
    	finally
    	{
    		in.close();
    	}
     	
	    
	    FileOutputStream out = null;
    	try
    	{             
    		 out = new FileOutputStream("c://temp//ppt_new_generated.xlsx");
    		 writeWorkBook.write(out); 
    		 
    		         
    	}
    	catch(Exception e)
    	{ 
    		e.printStackTrace();
    	}
    	finally
    	{
    		out.close();
    		System.out.println("********************************* Done with readWriteNewExcel ****************************************");
    		 
    	}
    	
	}
    
    private static void writeBadCell(XSSFWorkbook writeWorkBook, org.apache.poi.ss.usermodel.Cell writeCell)
    {
    	 Font font = writeWorkBook.createFont();
	     font.setBoldweight((short)20);
	     CellStyle style = writeWorkBook.createCellStyle();
	     style.setFillBackgroundColor(IndexedColors.YELLOW.getIndex());
	     style.setFillPattern(CellStyle.THIN_FORWARD_DIAG);
	     style.setFont(font);
	     writeCell.setCellStyle(style);
	     writeCell.setCellValue("*** Transformation warning **************");
	     return;
    }
}
